package net.mysoft.framework.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;



public class Operate {

	public void readExcel() throws IOException {
			InputStream inputStream = new FileInputStream("C:\\Users\\DAGONG\\Desktop\\13年签约项目梳理版本.xls");
			HSSFWorkbook wb = new HSSFWorkbook(inputStream);
			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row = null;
			HSSFCell cell1 = null;
			HSSFCell cell2 = null;
			HSSFCell cell3 = null;
			HSSFCell cell4 = null;
			HSSFCell cell5 = null;
			HSSFCell cell6 = null;
			String string1 = null;
			String string2 = null;
			String string3 = null;
			String string4 = null;
			String string5 = null;
			String string6 = null;
			int rowCount = sheet.getLastRowNum() + 10;
			NumberFormat nf = NumberFormat.getInstance();
			nf.setGroupingUsed(false);
			for (int i = 1; i < rowCount; i++) {
				try {
					row = sheet.getRow((short) i);
					cell1 = row.getCell(0);
					cell2 = row.getCell(1);
					cell3 = row.getCell(7);
					cell4 = row.getCell(8);
					cell5 = row.getCell(9);
					cell6 = row.getCell(2);
					if(cell4 == null){
					}
					cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
					string1 = cell1.getStringCellValue();
					string2 = cell2.getStringCellValue();
					string3 = cell3.getStringCellValue();
					string4 = cell4.getStringCellValue();
					string5 = cell5.getStringCellValue();
					string6 = cell6.getStringCellValue();
					if(!string2.equals("[2013]0048号") && !string2.equals("[2013]0319号") && !string2.equals("[2013]0435号") && !string2.equals("[2013]0461号") && !string2.equals("[2013]0588号") && !string2.equals("[2013]0998号")){
						//System.out.print(string6);
						this.findRenWuJBXX(string2,string1);
						System.out.println();
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	

	public String findRenWuJBXX(String hetongh,String string1) throws SQLException, ClassNotFoundException{
		String renwujbxxbh = "";
		String sql = "select * from renwujbxx where hetongh not like '%sd%' and  hetongh like '%/" + hetongh.substring(0, hetongh.indexOf("]")) + "/]" + hetongh.substring(hetongh.indexOf("]") + 1) + "%' escape '/'";
//		System.out.println(sql);
		Class.forName("net.sourceforge.jtds.jdbc.Driver");
		Connection connection =	DriverManager.getConnection("jdbc:jtds:sqlserver://10.10.10.173:1433/dagongcredit", "sa", "dagong");
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		System.out.println("--hetongh--------" + hetongh + "--------");
		while (rs.next()) {
//			System.out.print(rs.getString("qiyemc") + "	");
//			System.out.print(rs.getString("heTongh") + "	");
//			System.out.println(rs.getString("bianhao"));
			System.out.println("update renwush_dggj set renhangxmbh = '" + string1 + "' where  renhangxmbh is null and renwujbxxbh = '" + rs.getString("bianhao") + "' ");
		}
		return renwujbxxbh;
	}
	
	public List  findRenWuJBXXhth() throws SQLException, ClassNotFoundException{
		List list = new ArrayList();
		String sql = "select * from renwujbxx where hetongh is not null and hetongh != ''";
		Class.forName("net.sourceforge.jtds.jdbc.Driver");
		Connection connection =	DriverManager.getConnection("jdbc:jtds:sqlserver://10.10.10.173:1433/dagongcredit", "sa", "dagong");
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			String bianhao = rs.getString("bianhao");
			String hetongh = rs.getString("hetongh");
			hetongh = hetongh.trim();
			hetongh = hetongh.replaceAll(" ", "");
			String sql2 = "";
			String hao = "";
			if(hetongh.indexOf("]") > 0 && hetongh.indexOf( "号") > 0){
				hao = hetongh.substring(hetongh.indexOf("]") + 1,hetongh.indexOf( "号"));
				int length = hao.length();
				if(length == 3){
					hao = "0" + hao; 
					hetongh = hetongh.substring(0,hetongh.indexOf("]") + 1) + hao + "号";
				}
			}
			if(hetongh.substring(0, 1).equals("D")){
				hetongh = "大公合" + hetongh;
			}
			if(hetongh.contains("大公D")){
				hetongh = hetongh.substring(0, hetongh.indexOf("D")) + "合" + hetongh.substring(hetongh.indexOf("D"));
				
			}
			if(!hetongh.endsWith("号")){
				sql2 = "update renwujbxx set hetongh = '" + hetongh + "号' where bianhao = '" + bianhao + "'";
			}else{
				sql2 = "update renwujbxx set hetongh = '" + hetongh + "' where bianhao = '" + bianhao + "'";
			}
			System.out.println(sql2);
			list.add(sql2);
		}
		
		
		return list;
	}
	
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Operate o = new Operate();
		try {
			o.readExcel();
//			o.findRenWuJBXXhth();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
